package com.fcscs.cservice.dao.hibernate;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.DistinctRootEntityResultTransformer;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.fcscs.core.dao.ConnectionManager;
import com.fcscs.core.domain.BaseEntity.DBLoc;
import com.fcscs.cservice.common.CServiceCommonEnums.GuestProfileType;
import com.fcscs.cservice.common.CServiceCommonEnums.ReservationStatus;
import com.fcscs.cservice.dao.GuestProfileDao;
import com.fcscs.cservice.domain.ContactInfo;
import com.fcscs.cservice.domain.GuestProfile;
import com.fcscs.cservice.domain.PreferenceCode;
import com.fcscs.cservice.domain.SpecialCode;
import com.fcscs.cservice.domain.VIP;
import com.fcscs.cservice.domain.model.ContactInfoView;
import com.fcscs.cservice.domain.model.GuestProfileFindView;
import com.fcscs.cservice.domain.model.GuestProfileView;
import com.fcscs.cservice.domain.model.GuestProfileViewForFile;
import com.fcscs.cservice.domain.model.ReservationView;

@Repository("guestProfileDao")
public class GuestProfileDaoHibernate implements GuestProfileDao {

	@Autowired
	private ConnectionManager connMgr;
	private static DBLoc GPDBLoc = new GuestProfile().getDBLoc();

	private static final String FIND_GPV = " select distinct gp.*, ci.*, s.state state, c.country country, rm.room_number room_number, " +
			" rsv.arrival_date_time arrival_date_time, rsv.departure_date_time departure_date_time, rsv.id reservation_id from guest_profile gp ";
	private static final String FIND_GPV_JOIN_CI = " left outer join contact_info ci on gp.primary_contact_info_id = ci.id ";
	private static final String FIND_GPV_JOIN_CONF = " left outer join state s on ci.state_id = s.id left outer join country c on ci.country_id = c.id ";
	private static final String FIND_GPV_CNT = " select count(distinct gp.id) from guest_profile gp ";
	private static final String FIND_GPV_JOIN_RSV = " left outer join reservation rsv on rsv.guest_profile_id = gp.id and rsv.reservation_status_id in (:status) " +
			" left outer join room rm on rm.id = rsv.room_id ";
	private static final String FIND_GPV_JOIN_MEM = " inner join membership mb on mb.guest_profile_id = gp.id ";
	private static final String FIND_GPV_WHERE = " where gp.property_id = :propId ";
	private static final String FIND_GPV_BY_FNAME = " and gp.first_name like :fname ";
	private static final String FIND_GPV_BY_LNAME = " and gp.last_name like :lname ";
	private static final String FIND_GPV_BY_MOBL = " and gp.mobile like :mobile ";
	private static final String FIND_GPV_BY_ROOM = " and rm.room_number like :room ";
	private static final String FIND_GPV_BY_MEM = " and mb.number like :member ";
	private static final String FIND_GPV_BY_PHO = " and (ci.phone like :phone or ci.phone2 like :phone) ";
	private static final String FIND_GPV_GROUP = " group by gp.id ";


	private static final String GET_GP_VIEW = " select gp.*, ci.*, s.state state, c.country country from guest_profile gp " +
			" left outer join contact_info ci on gp.primary_contact_info_id = ci.id " +
			" left outer join state s on ci.state_id = s.id left outer join country c on ci.country_id = c.id " +
			" where gp.id = :id ";
	private static final String GET_GP_VIEW_FILE = " select gp.*, ci.*, s.state state, c.country country, lg.language, tt.title, nt.nationality " +
			" from guest_profile gp left outer join contact_info ci on gp.primary_contact_info_id = ci.id " +
			" left outer join state s on ci.state_id = s.id left outer join country c on ci.country_id = c.id " +
			" left outer join language lg on gp.language_id = lg.id left outer join title tt on gp.title_id = tt.id " +
			" left outer join nationality nt on gp.nationality_id = nt.id where gp.id = :id ";
	private static final String GET_MEMSHIP_BULK = " select id, guest_profile_id, name, number, level, rank from membership m " +
			" where m.guest_profile_id in (:gpIds) order by m.guest_profile_id, m.rank desc ";
	private static final String GET_CONTACT_INFOS = " select ci.*, c.country country, s.state state from contact_info ci " +
			" inner join guest_contact_info gci on gci.contact_info_id = ci.id left outer join state s on s.id = ci.state_id " +
			" left outer join country c on c.id = ci.country_id where gci.guest_profile_id = :gpId ";
	private static final String GET_PREF_CODES = " select pc.* from guest_preference_code gpc " +
			" inner join preference_code pc on gpc.preference_code_id = pc.id where gpc.guest_profile_id = :gpId ";
	private static final String GET_SPEC_CODES = " select sc.* from guest_special_code gsc " +
			" inner join special_code sc on gsc.special_code_id = sc.id where gsc.guest_profile_id = :gpId ";
	private static final String GET_VIPS = " select v.* from guest_vip gv inner join vip v on gv.guest_profile_id = v.id" +
			" where gv.guest_profile_id = :gpId ";
	private static final String GET_PREF_CODES_S = " select pc.preference_code from guest_preference_code gpc " +
			" inner join preference_code pc on gpc.preference_code_id = pc.id where gpc.guest_profile_id = :gpId ";
	private static final String GET_SPEC_CODES_S = " select sc.special_code from guest_special_code gsc " +
			" inner join special_code sc on gsc.special_code_id = sc.id where gsc.guest_profile_id = :gpId ";
	private static final String GET_VIPS_S = " select v.vip_code from guest_vip gv inner join vip v on gv.guest_profile_id = v.id" +
			" where gv.guest_profile_id = :gpId ";
	private static final String INS_GUES_CI = " insert into guest_contact_info (guest_profile_id, contact_info_id) values (:gpId, :ciId) ";

	private static final String DELIMITER = ", ";

	@SuppressWarnings("unchecked")
	@Override
	public List<GuestProfileFindView> findGuestProfile(long propId, String firstName, String lastName, String roomNo, String phoneNo,
			String mobileNo, String memberNo, int start, int resultCnt, boolean inHouseGuestOnly) {
		Query q = buildGPSearch(propId, firstName, lastName, roomNo, phoneNo, mobileNo, memberNo, true, inHouseGuestOnly);
		q.setMaxResults(resultCnt);
		q.setFirstResult(start);
		List<GuestProfileFindView> list = q.list();
		for (GuestProfileFindView v : list) {
			if (v.getRoomNo() != null) {
				v.setGuestType(GuestProfileType.IN_HOUSE_GUEST.toString());
			} else {
				v.setGuestType(GuestProfileType.NON_IN_HOUSE_GUEST.toString());
			}
		}
		return list;
	}

	@Override
	public int findGuestProfileCnt(long propId, String firstName, String lastName, String roomNo, String phoneNo,
			String mobileNo, String memberNo, boolean inHouseGuestOnly) {
		Query q = buildGPSearch(propId, firstName, lastName, roomNo, phoneNo, mobileNo, memberNo, false, inHouseGuestOnly);
		return ((Number) q.uniqueResult()).intValue();
	}

	@SuppressWarnings("serial")
	private Query buildGPSearch(long propId, String firstName, String lastName, String roomNo, String phoneNo,
			String mobileNo, String memberNo, boolean isSearch, boolean inHouseGuestOnly) {
		firstName = trimStringCond(firstName);
		lastName = trimStringCond(lastName);
		roomNo = trimStringCond(roomNo);
		if (inHouseGuestOnly) {
			roomNo = "";
		}
		phoneNo = trimStringCond(phoneNo);
		mobileNo = trimStringCond(mobileNo);
		memberNo = trimStringCond(memberNo);
		
		StringBuffer sb = new StringBuffer();
		if (isSearch) {
			sb.append(FIND_GPV).append(FIND_GPV_JOIN_CI).append(FIND_GPV_JOIN_CONF).append(FIND_GPV_JOIN_RSV); 
		} else {
			sb.append(FIND_GPV_CNT).append(phoneNo != null ? FIND_GPV_JOIN_CI : "").append(roomNo != null ? FIND_GPV_JOIN_RSV : "");
		}
		sb.append(memberNo != null ? FIND_GPV_JOIN_MEM : "")
			.append(FIND_GPV_WHERE).append(firstName != null ? FIND_GPV_BY_FNAME : "").append(lastName != null ? FIND_GPV_BY_LNAME : "")
			.append(roomNo != null ? FIND_GPV_BY_ROOM : "").append(phoneNo != null ? FIND_GPV_BY_PHO : "")
			.append(mobileNo != null ? FIND_GPV_BY_MOBL : "").append(memberNo != null ? FIND_GPV_BY_MEM : "");
		sb.append(isSearch ? FIND_GPV_GROUP : "");
		
		SQLQuery q = connMgr.getCurrentSession(GPDBLoc).createSQLQuery(sb.toString());
		q.setLong("propId", propId);
		if (isSearch || roomNo != null) {
			q.setParameterList("status", ReservationStatus.getInRoomStatusList());
		}
		if (firstName != null) {
			q.setString("fname", firstName + "%");
		}
		if (lastName != null) {
			q.setString("lname", lastName + "%");
		}
		if (roomNo != null) {
			q.setString("room", roomNo + "%");
		}
		if (phoneNo != null) {
			q.setString("phone", phoneNo + "%");
		}
		if (mobileNo != null) {
			q.setString("mobile", mobileNo + "%");
		}
		if (memberNo != null) {
			q.setString("member", memberNo + "%");
		}
		if (isSearch) {
			q.addEntity("gp", GuestProfileFindView.class);
			q.addJoin("ci", "gp.contactInfo");
			q.setResultTransformer(new DistinctRootEntityResultTransformer() {
			    @Override
				public Object transformTuple(Object[] tuple, String[] aliases) {
			        return tuple[0];
				}
			});
		}
		return q;
	}

	private String trimStringCond(String cond) {
		if (cond != null) {
			cond = cond.trim();
			if ("".equals(cond)) {
				cond = null;
			}
		}
		return cond;
	}

	@SuppressWarnings("unchecked")
	@Override
	public <E extends GuestProfile> E getGuestProfile(long id, Class<E> cls) {
		return (E)connMgr.getCurrentSession(GPDBLoc).get(cls, id);
	}
	
	@Override
	public <E extends GuestProfile> E updateGuestProfile(E guestProfile) {
		Session s = connMgr.getCurrentSession(GPDBLoc);
		s.saveOrUpdate(guestProfile);
		return guestProfile;
	}
	
	@Override
	public ContactInfo getContactInfo(long id) {
		return (ContactInfo)connMgr.getCurrentSession(GPDBLoc).get(ContactInfo.class, id);
	}

	@Override
	public ContactInfo updateContactInfo(ContactInfo contactInfo) {
		Session s = connMgr.getCurrentSession(GPDBLoc);
		s.saveOrUpdate(contactInfo);
		return contactInfo;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Map<Long, List<ReservationView.Membership>> getMemberships(Set<Long> gpIds) {
		if (gpIds == null || gpIds.size() <= 0) {
			return null;
		}
		SQLQuery q = connMgr.getCurrentSession(GPDBLoc).createSQLQuery(GET_MEMSHIP_BULK);
		q.setParameterList("gpIds", gpIds);
		q.addEntity(ReservationView.Membership.class);
		List<ReservationView.Membership> memList = q.list();
		Map<Long, List<ReservationView.Membership>> memMap = new HashMap<Long, List<ReservationView.Membership>>();
		if (memList != null && memList.size() > 0) {
			long gpId = -1;
			List<ReservationView.Membership> mems = null;
			for (ReservationView.Membership mem: memList) {
				if (mems == null) {
					gpId = mem.getGuestProfileId();
					mems = new ArrayList<ReservationView.Membership>();
				} else if (gpId != mem.getGuestProfileId()) {
					memMap.put(gpId, mems);
					gpId = mem.getGuestProfileId();
					mems = new ArrayList<ReservationView.Membership>();
				}
				mems.add(mem);
			}
			if (mems != null) {
				memMap.put(gpId, mems);
			}
		}
		return memMap;
	}

	@SuppressWarnings("serial")
	@Override
	public GuestProfileView getGuestProfileView(long id) {
		SQLQuery q = connMgr.getCurrentSession(GPDBLoc).createSQLQuery(GET_GP_VIEW);
		q.setLong("id", id);
		q.addEntity("gp", GuestProfileView.class);
		q.addJoin("ci", "gp.contactInfo");
		q.setResultTransformer(new DistinctRootEntityResultTransformer() {
		    @Override
			public Object transformTuple(Object[] tuple, String[] aliases) {
		        return tuple[0];
			}
		});
		return (GuestProfileView)q.uniqueResult();
	}

	@SuppressWarnings("serial")
	@Override
	public GuestProfileViewForFile getGuestProfileViewForFile(long id) {
		SQLQuery q = connMgr.getCurrentSession(GPDBLoc).createSQLQuery(GET_GP_VIEW_FILE);
		q.setLong("id", id);
		q.addEntity("gp", GuestProfileViewForFile.class);
		q.addJoin("ci", "gp.contactInfo");
		q.setResultTransformer(new DistinctRootEntityResultTransformer() {
		    @Override
			public Object transformTuple(Object[] tuple, String[] aliases) {
		        return tuple[0];
			}
		});
		return (GuestProfileViewForFile)q.uniqueResult();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ContactInfo> getAddresses(long guestProfileId) {
		SQLQuery sql = connMgr.getCurrentSession(GPDBLoc).createSQLQuery(GET_CONTACT_INFOS);
		sql.addEntity(ContactInfo.class);
		sql.setLong("gpId", guestProfileId);
		return sql.list();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<PreferenceCode> getPrefCodes(long guestProfileId) {
		SQLQuery sql = connMgr.getCurrentSession(GPDBLoc).createSQLQuery(GET_PREF_CODES);
		sql.addEntity(PreferenceCode.class);
		sql.setLong("gpId", guestProfileId);
		return sql.list();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<SpecialCode> getSpecialCodes(long guestProfileId) {
		SQLQuery sql = connMgr.getCurrentSession(GPDBLoc).createSQLQuery(GET_SPEC_CODES);
		sql.addEntity(SpecialCode.class);
		sql.setLong("gpId", guestProfileId);
		return sql.list();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<VIP> getVIPs(long guestProfileId) {
		SQLQuery sql = connMgr.getCurrentSession(GPDBLoc).createSQLQuery(GET_VIPS);
		sql.addEntity(VIP.class);
		sql.setLong("gpId", guestProfileId);
		return sql.list();
	}

	private static String transListToString(List<String> sList) {
		if (sList != null) {
			StringBuffer sb = new StringBuffer();
			boolean first = true;
			for (String s : sList) {
				if (!first) {
					sb.append(DELIMITER);
				} else {
					first = false;
				}
				sb.append(s);
			}
			return sb.toString();
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ContactInfoView> getContactInfoViews(long guestProfileId) {
		SQLQuery sql = connMgr.getCurrentSession(GPDBLoc).createSQLQuery(GET_CONTACT_INFOS);
		sql.addEntity(ContactInfoView.class);
		sql.setLong("gpId", guestProfileId);
		return sql.list();
	}

	@SuppressWarnings("unchecked")
	@Override
	public String getPrefCodesString(long guestProfileId) {
		SQLQuery sql = connMgr.getCurrentSession(GPDBLoc).createSQLQuery(GET_PREF_CODES_S);
		sql.addScalar("preference_code", Hibernate.STRING);
		sql.setLong("gpId", guestProfileId);
		return transListToString(sql.list());
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public String getSpecialCodesString(long guestProfileId) {
		SQLQuery sql = connMgr.getCurrentSession(GPDBLoc).createSQLQuery(GET_SPEC_CODES_S);
		sql.addScalar("special_code", Hibernate.STRING);
		sql.setLong("gpId", guestProfileId);
		return transListToString(sql.list());
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public String getVIPCodesString(long guestProfileId) {
		SQLQuery sql = connMgr.getCurrentSession(GPDBLoc).createSQLQuery(GET_VIPS_S);
		sql.addScalar("vip_code", Hibernate.STRING);
		sql.setLong("gpId", guestProfileId);
		return transListToString(sql.list());
	}

	@Override
	public boolean updateGuestContactInfo(Long guestProfileId, Long contactInfoId) {
		if (guestProfileId == null || contactInfoId == null) {
			return false;
		}
		SQLQuery q = connMgr.getCurrentSession(GPDBLoc).createSQLQuery(INS_GUES_CI);
		q.setLong("gpId", guestProfileId);
		q.setLong("ciId", contactInfoId);
		return q.executeUpdate() == 1;
	}
}
